﻿using SQLite;
using System.Collections.Generic;
using System.Linq;
using Xamarin.Forms;
using System.Collections.ObjectModel;
using Cashbook.Models;
using System;
using System.Threading.Tasks;
using Cashbook.ViewModels;

namespace Cashbook.Services
{
	public class BillsDataAccess
	{
		readonly SQLiteAsyncConnection database;
       
		//public ObservableCollection<Bill> Bills { get; set; }
		public BillsDataAccess()
		{
			database = Database.GetDBConnection();
		}

		public async Task<ObservableCollection<Bill>> GetBillsOC()
		{
			var collection = new ObservableCollection<Bill>();
			var items = await database.Table<Bill>().ToListAsync();
			foreach (var item in items)
			{
				collection.Add(item);
			}

			return collection;
		}


		public Task<List<Bill>> GetBillsAsync()
		{
			return database.Table<Bill>().ToListAsync();
		}


        public async Task<IEnumerable<MonthStaticsVM>> GetStatisticsByMonth()
		{

			var bills = await database.Table<Bill>().ToListAsync();
            //var customers = database.Table<Customer>().ToListAsync().Result;
            var statementlist = await database.Table<Statement>().ToListAsync();
            var statements = statementlist.GroupBy(m => m.BillId)
									 .Select(m => new StatementStaticsVM
									 {
										 BillId = m.First().BillId,
										 Amount = m.Sum(c => c.Amount)
									 });

			var result = (from bill in bills						
						join s in statements on bill.Id equals s.BillId into stats
						from st in stats.DefaultIfEmpty()
                         orderby bill.AddedDate descending
						select new BillVM
						{
							Id = bill.Id,
							Title = bill.Title,						
							Amount = bill.Amount,
							PayedAmount = (st != null ? st.Amount : 0),
							AddedDate = bill.AddedDate
						}).GroupBy(l => l.AddedDate.Month)
                          .Select(cl => new MonthStaticsVM
			{
			       Month = cl.First().AddedDate.Month,   
			  Amount = cl.Sum(c => c.Amount),
                PayedAmount = cl.Sum(c=>c.PayedAmount)
                         
            }).AsEnumerable();

			//return query;

			//var bills = await database.Table<Bill>().ToListAsync();
   //         var statements = await database.Table<Statement>().ToListAsync();

			//List<MonthStaticsVM> result =  bills
   //             .GroupBy(l => l.AddedDate.Month)
			//.Select(cl => new MonthStaticsVM
			//{
		 //       Month = cl.First().AddedDate.Month,	
			//	Amount = cl.Sum(c => c.Amount)
			//}).ToList();
			return result;

		}

        public IEnumerable<BillVM> GetBillsWithCustomer(bool isPayed)
		{		
            
			var bills = database.Table<Bill>().ToListAsync().Result;
			var customers = database.Table<Customer>().ToListAsync().Result;
            var statements = database.Table<Statement>().ToListAsync().Result.GroupBy(m=>m.BillId)
                                     .Select(m => new StatementStaticsVM {
                BillId = m.First().BillId, 
                Amount = m.Sum(c=>c.Amount)
            });
			var query = from bill in bills
						join customer in customers
                on bill.CustomerId equals customer.Id
                       join s in statements on bill.Id equals s.BillId into stats from st in stats.DefaultIfEmpty()

						where bill.IsPayed == isPayed
						select new BillVM
						{
							Id = bill.Id,
							Title = bill.Title,
							CustomerName = customer.CompanyName,
							Amount = bill.Amount,
                            PayedAmount = (st != null ? st.Amount : 0),
							AddedDate = bill.AddedDate
						};

            return query.OrderByDescending(m=>m.AddedDate).AsEnumerable();


		}
		public ObservableCollection<BillVM> GetBillsWithCustomerOC(bool isPayed)
		{
            var bills = database.Table<Bill>().ToListAsync().Result;
            var customers = database.Table<Customer>().ToListAsync().Result;
            var query = from bill in bills
                        join customer in customers
                on bill.CustomerId equals customer.Id
                        where bill.IsPayed == isPayed
                        select new BillVM
                        {
                            Id = bill.Id,
                            Title = bill.Title,
                CustomerName = customer.CompanyName,
                Amount = bill.Amount,
                AddedDate = bill.AddedDate
            };

            var collection = new ObservableCollection<BillVM>();
			foreach (var item in query.ToList())
			{
				collection.Add(item);
			}

			return collection;
            

		}


		public Task<Bill> GetBillAsync(int id)
		{
			return database.Table<Bill>().Where(i => i.Id == id).FirstOrDefaultAsync();
		}

		public Task<int> SaveBillAsync(Bill item)
		{

			if (item.Id != 0)
			{
				return database.UpdateAsync(item);
			}
			else
			{
				return database.InsertAsync(item);
			}

		}

		public Task<int> DeleteBillAsync(Bill item)
		{
			return database.DeleteAsync(item);
		}

        public void DeleteBillWithStatementAsync(Bill item)
		{
            
             database.RunInTransactionAsync((SQLiteConnection connection) =>{
                var statements = connection.Table<Statement>().Where(mn => mn.BillId == item.Id).ToList();
				foreach (var s in statements)
				{
					connection.Delete(s);
				}
                connection.Delete(item);
               
            });  

            //return database.DeleteAsync(item);
		}
	}
}
